-- @owner: zhao-pengcheng-g
-- @date: 2022/01/24
-- @testpoint:约束触发器 constraint  deferrable  initially immediate,before

--step1:建表，插入数据;expect:成功
drop table if exists table_clob_0011;
create table table_clob_0011(c_id int not null,b_clob clob) with (storage_type=ustore);
insert into table_clob_0011 values(1,'35466');
insert into table_clob_0011 values(2,'3546213457869708234444479806');
insert into table_clob_0011 values(3,'3546213457864444479806');
insert into table_clob_0011 values(4,'35463457686543122453536');
insert into table_clob_0011 values(5,'354621345784533669708234444479806');
insert into table_clob_0011 values(6,'354621345743567378567864444479806');
insert into table_clob_0011 values(7,'100000');
insert into table_clob_0011 values(8,'101000');

--step2:创建函数，创建触发器;expect:成功
create or replace function tri_update_func_0011() returns trigger as
$$
declare
declare  cc_clob clob;
begin
 update table_clob_0011 set b_clob= b_clob || '111111111' where c_id=1;
return new;
end;
$$ language plpgsql;
/
create constraint trigger trigger_table_clob_0011 after insert on table_clob_0011
deferrable  initially deferred
for each row
execute procedure tri_update_func_0011();

--step3:插入数据，触发trigger;expect:成功
select * from table_clob_0011 where c_id=1;
insert into table_clob_0011 values(1,'3566');
select * from table_clob_0011 where c_id=1;

--step4:环境清理;expect:成功
drop trigger trigger_table_clob_0011 on table_clob_0011 cascade;
drop table  table_clob_0011 cascade;
drop function tri_update_func_0011;